with waybill_incre as (
    select dt
         , customer_code
         , receiver_province_id
         , kg_part
         , kg_part_two
         , sum(nvl(package_charge_weight, 0)) as weight_total
         , count(waybill_no)                  as waybill_num
    from (
             select dt
                  , customer_code
                  , receiver_province_id
                  , waybill_no
                  , package_charge_weight
                  , CASE
                        WHEN nvl(package_charge_weight, 0) >= 0 AND nvl(package_charge_weight, 0) <= 0.3
                            then 0.3
                        WHEN nvl(package_charge_weight, 0) > 0.3 AND nvl(package_charge_weight, 0) <= 0.5
                            then 0.5
                        WHEN nvl(package_charge_weight, 0) > 0.5 AND nvl(package_charge_weight, 0) <= 1
                            then 1
                        WHEN nvl(package_charge_weight, 0) > 1 AND nvl(package_charge_weight, 0) <= 2
                            then 2
                        WHEN nvl(package_charge_weight, 0) > 2 AND nvl(package_charge_weight, 0) <= 3
                            then 3
                        WHEN nvl(package_charge_weight, 0) > 3 AND nvl(package_charge_weight, 0) <= 5
                            then 5
                        WHEN nvl(package_charge_weight, 0) > 5 AND nvl(package_charge_weight, 0) <= 10
                            then 10
                        WHEN nvl(package_charge_weight, 0) > 10
                            then 999
                 END as kg_part     --重量段:左开右闭  0-0.3,0.3-0.5,0.5-1,1-2,2-3,3-5,5-10,999(>10)
                  , CASE
                        WHEN nvl(package_charge_weight, 0) >= 0 AND nvl(package_charge_weight, 0) <= 0.3
                            then '0-0.3kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 0.3 AND nvl(package_charge_weight, 0) <= 0.5
                            then '0.3-0.5kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 0.5 AND nvl(package_charge_weight, 0) <= 1
                            then '0.5-1kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 1 AND nvl(package_charge_weight, 0) <= 2
                            then '1-2kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 2 AND nvl(package_charge_weight, 0) <= 3
                            then '2-3kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 3 AND nvl(package_charge_weight, 0) <= 5
                            then '3-5kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 5 AND nvl(package_charge_weight, 0) <= 10
                            then '5-10kg(含)'
                        WHEN nvl(package_charge_weight, 0) > 10
                            then '10kg以上'
                 END as kg_part_two --重量段:左开右闭  0-0.3,0.3-0.5,0.5-1,1-2,2-3,3-5,5-10,999(>10)
             from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -14)
               and dt <= '{{ execution_date | cst_ds }}'
               and customer_code is not null
               and receiver_province_id is not null
         ) a
    group by dt
           , customer_code
           , receiver_province_id
           , kg_part
           , kg_part_two
)
insert
overwrite
table
jms_dm.dm_cusc_entity_customer_network_province_kg_summary_day_dt
partition
(
dt
)
select /* +BROADCAST(t2) */
    t2.customer_entity_code                                           as customer_entity_code   --客户实体code
     , t2.customer_entity_name                                        as customer_entity_name   --客户实体name
     , t2.customer_network_code                                       as customer_network_code  --客户网点code
     , t2.customer_network_name                                       as customer_network_name  --客户网点name
     , t2.customer_agent_code                                         as customer_agent_code    --客户代理区code
     , t2.customer_agent_name                                         as customer_agent_name    --客户代理区name
     , t2.customer_virt_code                                          as customer_virt_code     --客户虚拟代理区code
     , t2.customer_virt_name                                          as customer_virt_name     --客户虚拟代理区name
     , t1.customer_code                                               as customer_code          --客户code
     , t2.customer_name                                               as customer_name          --客户name
     , t1.receiver_province_id                                        as receiver_province_id   --签收省份id
     , t3.receiver_province_name                                      as receiver_province_name --签收省份name
     , t1.kg_part                                                     as kg_part                --重量段
     , t1.kg_part_two                                                 as kg_part_two            --重量段
     , t1.weight_total                                                as weight_total           --总重量
     , t1.waybill_num                                                 as waybill_num            --揽收量
     , sum(t1.waybill_num) over (partition by t1.dt,t1.customer_code) as customer_waybill_num   --客户揽收量
     , sum(if(t2.customer_entity_code is not null, t1.waybill_num, 0))
           over (partition by t1.dt,t2.customer_entity_code)          as entity_waybill_num     --客户实体揽收量
     , t1.dt                                                          as date_time              --日期(运单录入时间)
     , t4.month_id                                                    as year_month             --年月
     , t4.week_id                                                     as year_week              --年周
     , t4.week_first_day                                              as week_first_day         --周第一天
     , t4.month_fst_date                                              as month_fst_date         --月第一天
     , from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')         as update_time
     , t1.dt                                                                                    --分区
from waybill_incre t1
         left join
(
    select t1.code        as customer_code
         , t1.name        as customer_name
         , t1.entity_code as customer_entity_code
         , t1.entity_name as customer_entity_name
         , t2.code        as customer_network_code
         , t2.name        as customer_network_name
         , t2.agent_code  as customer_agent_code
         , t2.agent_name  as customer_agent_name
         , t2.virt_code   as customer_virt_code
         , t2.virt_name   as customer_virt_name
    from jms_dim.dim_lmdm_sys_customer t1
             left join jms_dim.dim_network_whole_massage t2
                       on t1.network_id = t2.id
    where t1.code is not null
) t2
on t1.customer_code = t2.customer_code
         left join
(
    select id
         , cn_name as receiver_province_name
    from jms_dim.dim_lmdm_sys_area
    where id is not null
    group by id
           , cn_name
) t3
on t1.receiver_province_id = t3.id
         left join
(
    select date_mid_desc
         , month_id
         , week_id
         , date_add(date_mid_desc, -daynumber_of_week + 1) as week_first_day
         , month_fst_date
    from jms_dim.dim_date_info_base
    where date_mid_desc >= date_add('{{ execution_date | cst_ds }}', -14)
      and date_mid_desc <= '{{ execution_date | cst_ds }}'
) t4
on t1.dt = t4.date_mid_desc
    distribute by dt
   , pmod(hash(customer_code), 10);
